package com.mycity.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mycity.bean.Gamer;
import com.mycity.bean.User;
import com.mycity.gamification.AchievementSystem;
import com.mycity.gamification.LevelCalculator;
import com.mycity.mapper.UserRowMapper;
import com.mysql.jdbc.PreparedStatement;


public class UserDao extends RDSDao{
	
	GameDao gameDao = new GameDao();

	public User retrieveUserByEmail(String email, String password) {
		User foundUser = new User();
		Object params[] = { email, password };
	    List<User> users = this.getTemplate().query("SELECT * from User where email=? and password=?", params, new UserRowMapper());
	    
	    	    
	    if(users == null || users.isEmpty())
	    	return new User();
	    else
	    	foundUser = users.get(0);
	    
	    Gamer gamer = gameDao.getGamer(foundUser.getUserId());
	    AchievementSystem.update(foundUser, gamer);
    
	    return foundUser;
	}
	
	public User getUserById(int userId) {
		User foundUser = new User();
	    List<User> users = this.getTemplate().query("SELECT * from User where userId="+userId, new UserRowMapper());
	    
	    if(users == null || users.isEmpty())
	    	return new User();
	    else
	    	foundUser = users.get(0);
	    
	    Gamer gamer = gameDao.getGamer(userId);
	    AchievementSystem.update(foundUser, gamer);
    
	    return foundUser;
	}
	
	public User addUser(final String username, final String name, final String surname, final String email, final String password) {
		User addedUser;
	    final String insertSQL = "INSERT INTO User(username, name, surname, email, password) VALUES(?,?,?,?,?)";

		KeyHolder holder = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {           


			@Override
			public java.sql.PreparedStatement createPreparedStatement(
					Connection con) throws SQLException {
				PreparedStatement ps = (PreparedStatement) con.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, username);
				ps.setString(2, name);
				ps.setString(3, surname);
				ps.setString(4, email);
				ps.setString(5, password);
				return ps;
			}
		}, holder);

		int userId = holder.getKey().intValue();
	    Object params[] = { userId };
	    this.getTemplate().update("INSERT INTO Gamification(userId) VALUES(?)", params);
	    addedUser = this.retrieveUserByEmail(email, password);
	    return addedUser;
	}
	
	public boolean emailExists(String email){
		Object params[] = { email };
	    List<User> users = this.getTemplate().query("SELECT * from User where email=?", params, new UserRowMapper());
	    if(users == null || users.isEmpty())
	    	return false;
	    else
	    	return true;
	}
	
	public boolean usernameExists(String username){
		Object params[] = { username };
		List<User> users = this.getTemplate().query("SELECT * from User where username=?", params, new UserRowMapper());
		if(users == null || users.isEmpty())
	    	return false;
	    else
	    	return true;
	}	
	
	public void setBio(int userId, String bio){
		Object params[] = { bio, userId};
		this.getTemplate().update("UPDATE User SET bio=? WHERE userId=?", params);
	}
	
	public void setProfilePic(int userId, String profilePic){
		Object params[] = { profilePic, userId};
		this.getTemplate().update("UPDATE User SET profilePic=? WHERE userId=?", params);
	}
}
